﻿Imports log4net
Imports Equipment.DataAccess

Partial Public Class WorkOrder
    Private Shared logger As ILog = LogManager.GetLogger(GetType(WorkOrder))

#Region "Constants"
    Public Const FILTER_ALL As Integer = 0
#End Region

#Region "Properties"
    Private _db As EquipmentDataContext
    Public Property DBContext As EquipmentDataContext
        Get
            Return _db
        End Get
        Set(ByVal value As EquipmentDataContext)
            _db = value
        End Set
    End Property

    Private _measureList As List(Of WorkOrderMeasure)
    Public Property Measures As List(Of WorkOrderMeasure)
        Get
            If _measureList Is Nothing Then

                _measureList = New List(Of WorkOrderMeasure)

                For Each measure In (From m In Database.Instance.WorkOrderMeasures Where m.WorkOrderID = Me.ID)
                    _measureList.Add(measure)
                Next

            End If
            Return _measureList
        End Get
        Set(ByVal value As List(Of WorkOrderMeasure))
            _measureList = value
        End Set
    End Property


    Private _groupList As List(Of Group)
    Public Property Groups As List(Of Group)
        Get
            If _groupList Is Nothing Then

                _groupList = New List(Of Group)

                For Each groupItem In (From r In Database.Instance.WorkOrderGroups Where r.WorkOrderID = Me.ID Select r.Group)
                    _groupList.Add(groupItem)
                Next

            End If
            Return _groupList
        End Get
        Set(ByVal value As List(Of Group))
            _groupList = value
        End Set
    End Property

    Private _reviewGroupList As List(Of Group)
    Public Property ReviewGroups As List(Of Group)
        Get
            If _reviewGroupList Is Nothing Then

                _reviewGroupList = New List(Of Group)

                For Each groupItem In (From r In Database.Instance.WorkOrderReviewGroups Where r.WorkOrderID = Me.ID Select r.Group)
                    _reviewGroupList.Add(groupItem)
                Next

            End If
            Return _reviewGroupList
        End Get
        Set(ByVal value As List(Of Group))
            _reviewGroupList = value
        End Set
    End Property

#End Region

#Region "Public Section"

#Region "Retrieve For Authorized User"


    Public Shared Function RetrieveAllByStatusesFor(ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                     ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                      wo.Status = status Or wo.Status = secondStatus
    End Function

    Public Shared Function RetrieveAllByStatusesFor(ByVal groupIDs As List(Of Integer), _
                                    ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                        ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                             ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAllFor(groupIDs, assetTypeID, departmentID, _
                                        groupID, dueDateFrom, dueDateTo)) Where wo.Status = status Or wo.Status = secondStatus
    End Function

    Public Shared Function RetrieveAllByStatusesFor(ByVal groupIDs As List(Of Integer), _
                                                        ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                                            ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAllFor(groupIDs, 0, 0, 0, _
                                            dueDateFrom, dueDateTo)) Where wo.Status = status Or wo.Status = secondStatus
    End Function

    Public Shared Function RetrieveAllByStatusFor(ByVal groupIDs As List(Of Integer), _
                                                        ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                                            ByVal status As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAllFor(groupIDs, 0, 0, 0, _
                                            dueDateFrom, dueDateTo)) Where wo.Status = status
    End Function

    Public Shared Function RetrieveAllByStatusFor(ByVal groupIDs As List(Of Integer), _
                                                    ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                                        ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                                            ByVal status As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAllFor(groupIDs, assetTypeID, departmentID, FILTER_ALL, _
                                        dueDateFrom, dueDateTo)) Where wo.Status = status
    End Function

    Public Shared Function RetrieveAllByStatusFor(ByVal groupIDs As List(Of Integer), _
                                        ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                            ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                                 ByVal status As WorkOrderStatusType) As IEnumerable(Of WorkOrder)

        If status = WorkOrderStatusType.Open Then
            Return From wo In (RetrieveAllFor(groupIDs, assetTypeID, departmentID, _
                                            groupID, dueDateFrom, dueDateTo)) Where wo.Status <> WorkOrderStatusType.Complete

        Else
            Return From wo In (RetrieveAllFor(groupIDs, assetTypeID, departmentID, _
                                            groupID, dueDateFrom, dueDateTo)) Where wo.Status = status
        End If
    End Function



    Public Shared Function RetrieveAllByAssetTypeAndDepartmentIDFor(ByVal groupIDs As List(Of Integer), _
                                            ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                                ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        If assetTypeID <> FILTER_ALL AndAlso departmentID <> FILTER_ALL Then
            Return From wo In (RetrieveAllFor(groupIDs, dueDateFrom, dueDateTo)) Where wo.Asset.AssetTypeID = assetTypeID AndAlso wo.Asset.SiteDepartmentID = departmentID
        ElseIf assetTypeID <> FILTER_ALL Then
            Return From wo In (RetrieveAllFor(groupIDs, dueDateFrom, dueDateTo)) Where wo.Asset.AssetTypeID = assetTypeID
        ElseIf departmentID <> FILTER_ALL Then
            Return From wo In (RetrieveAllFor(groupIDs, dueDateFrom, dueDateTo)) Where wo.Asset.SiteDepartmentID = departmentID
        Else
            Return RetrieveAllFor(groupIDs, dueDateFrom, dueDateTo)
        End If

    End Function

    Public Shared Function RetrieveAllAndATGroupIDFor(ByVal groupIDs As List(Of Integer), _
                                             ByVal groupID As Integer, _
                                                ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        If groupID <> FILTER_ALL Then
            Return From wo In (RetrieveAllFor(groupIDs, dueDateFrom, dueDateTo)) Where wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0
        Else
            Return RetrieveAllFor(groupIDs, dueDateFrom, dueDateTo)
        End If

    End Function


    ''' <summary>
    ''' Retrieve All WorkOrders for Authorized user
    ''' </summary>
    ''' <param name="groupIDs"></param>
    ''' <param name="assetTypeID"></param>
    ''' <param name="departmentID"></param>
    ''' <param name="groupID"></param>
    ''' <param name="dueDateFrom"></param>
    ''' <param name="dueDateTo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function RetrieveAllFor(ByVal groupIDs As List(Of Integer), ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                            ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        If assetTypeID = 0 AndAlso departmentID = 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0
        ElseIf assetTypeID = 0 AndAlso departmentID = 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                            wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0
        ElseIf assetTypeID = 0 AndAlso departmentID > 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                    wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0 AndAlso _
                                                                        wo.Asset.SiteDepartmentID = departmentID
        ElseIf assetTypeID > 0 AndAlso departmentID = 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                    wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0 AndAlso _
                                                                        wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID = 0 AndAlso departmentID > 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                            wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0 AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID
        ElseIf assetTypeID > 0 AndAlso departmentID > 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                            wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0 AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID AndAlso _
                                                                                wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID > 0 AndAlso departmentID > 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0 AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID AndAlso _
                                                                                wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID > 0 AndAlso departmentID = 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                            wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0 AndAlso _
                                                                                            wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID > 0 AndAlso departmentID > 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0 AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID AndAlso _
                                                                                wo.Asset.AssetTypeID = assetTypeID
        Else
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0

        End If
    End Function

    Public Shared Function RetrieveAllFor(ByVal groupIDs As List(Of Integer), ByVal dueDateFrom As DateTime, _
                                              ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                            wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0
    End Function

    Public Shared Function RetrieveAllFor(ByVal groupIDs As List(Of Integer)) As IQueryable(Of WorkOrder)

        Return From wo In Database.Instance.WorkOrders Where wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0
    End Function
#End Region

#Region "General"
    Public Shared Function RetrieveAll(ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)
        Return RetrieveAll(0, 0, 0, dueDateFrom, dueDateTo)
    End Function


    ''' <summary>
    ''' Retrieve All Work Orders according filter
    ''' </summary>
    ''' <param name="assetTypeID"></param>
    ''' <param name="departmentID"></param>
    ''' <param name="groupID"></param>
    ''' <param name="dueDateFrom"></param>
    ''' <param name="dueDateTo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function RetrieveAll(ByVal assetTypeID As Integer, ByVal departmentID As Integer, ByVal groupID As Integer, _
                                            ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        If assetTypeID = 0 AndAlso departmentID = 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo
        ElseIf assetTypeID = 0 AndAlso departmentID = 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                     wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0
        ElseIf assetTypeID = 0 AndAlso departmentID > 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                    wo.Asset.SiteDepartmentID = departmentID
        ElseIf assetTypeID > 0 AndAlso departmentID = 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                    wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID = 0 AndAlso departmentID > 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                         wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0 AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID
        ElseIf assetTypeID > 0 AndAlso departmentID > 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                         wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0 AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID AndAlso _
                                                                                wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID > 0 AndAlso departmentID > 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                            wo.Asset.SiteDepartmentID = departmentID AndAlso _
                                                                                wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID > 0 AndAlso departmentID = 0 AndAlso groupID > 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                                     wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupID = gID) > 0 AndAlso _
                                                                                            wo.Asset.AssetTypeID = assetTypeID
        ElseIf assetTypeID = 0 AndAlso departmentID > 0 AndAlso groupID = 0 Then
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                                            wo.Asset.SiteDepartmentID = departmentID
        Else
            Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo
        End If
    End Function

    Public Shared Function RetrieveAllByStatus(ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                                    ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                                        ByVal status As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        If status = WorkOrderStatusType.Open Then
            Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where wo.Status <> WorkOrderStatusType.Complete
        Else
            Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where wo.Status = status
        End If
    End Function


    Public Shared Function RetrieveAllByStatusFor(ByVal groupIDs As List(Of Integer), ByVal status As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In Database.Instance.WorkOrders Where (wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0) AndAlso wo.Status = status
    End Function

    Public Shared Function RetrieveAllBySiteAndStatus(ByVal siteID As Integer, ByVal status As WorkOrderStatusType) As IQueryable(Of WorkOrder)
        Return From wo In Database.Instance.WorkOrders Where wo.Status = status AndAlso wo.Asset.SiteDepartment.ParentID <> 0 AndAlso wo.Asset.SiteDepartment.ParentID = siteID
    End Function


#End Region

    Public Shared Function RetrieveAll() As IEnumerable(Of WorkOrder)
        Dim db As EquipmentDataContext = Database.Instance
        Return db.WorkOrders.OrderBy(Function(wo) wo.DateDue)
    End Function

    Public Shared Function RetrieveAllBySiteAndStatuses(ByVal siteID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                                            ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAllBySiteID(siteID, dueDateFrom, dueDateTo)) Where wo.Status = status Or wo.Status = secondStatus
    End Function

    Public Shared Function RetrieveAllBySiteAndStatuses(ByVal siteID As Integer, ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                            ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                 ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where (wo.Status = status Or wo.Status = secondStatus) AndAlso _
                                wo.Asset.SiteDepartment.ParentID = siteID
    End Function

    Public Shared Function RetrieveAllBySiteAndStatusesAndReviewRole(ByVal siteID As Integer, ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                        ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                             ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where (wo.Status = status Or wo.Status = secondStatus) AndAlso _
                                wo.Asset.SiteDepartment.ParentID = siteID
    End Function

    Public Shared Function RetrieveAllByStatuses(ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, _
                                     ByVal status As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where wo.Status = status Or wo.Status = secondStatus
    End Function

    Public Shared Function RetrieveAllByStatus(ByVal status As WorkOrderStatusType, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IEnumerable(Of WorkOrder)
        Dim db As EquipmentDataContext = Database.Instance

        Return From wo In Database.Instance.WorkOrders Where wo.Status = status AndAlso wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo
    End Function

    Public Shared Function RetrieveAllPerformedBy(ByVal userId As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IEnumerable(Of WorkOrder)
        Dim db As EquipmentDataContext = Database.Instance

        Return From wo In Database.Instance.WorkOrders Where wo.Status = WorkOrderStatusType.Complete AndAlso wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo _
                                                                AndAlso wo.PerformedBy = userId
    End Function

    ''' <summary>
    ''' Retrieve All WorkOrders filtering by 2 statuses and due Date period
    ''' </summary>
    ''' <param name="firstStatus">WorkOrder Status</param>
    ''' <param name="secondStatus">WorkOrder Status</param>
    ''' <param name="dueDateFrom">Due Date From</param>
    ''' <param name="dueDateTo">Due Date To</param>
    ''' <returns>WorkOrder list</returns>
    ''' <remarks></remarks>
    Public Shared Function RetrieveAllByStatuses(ByVal firstStatus As WorkOrderStatusType, ByVal secondStatus As WorkOrderStatusType, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IEnumerable(Of WorkOrder)
        Dim db As EquipmentDataContext = Database.Instance

        Return From wo In Database.Instance.WorkOrders Where (wo.Status = firstStatus Or wo.Status = secondStatus) AndAlso wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo
    End Function

    Public Shared Function RetrieveAllByStatus(ByVal status As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Dim db As EquipmentDataContext = Database.Instance

        Return From wo In Database.Instance.WorkOrders Where wo.Status = status
    End Function

    ''' <summary>
    ''' Retrieve All WorkOrders filtering by 2 statuses
    ''' </summary>
    ''' <param name="statusFirst">WorkOrder Status</param>
    ''' <param name="statusSecond">WorkOrder Status</param>
    ''' <returns>WorkOrder list</returns>
    ''' <remarks></remarks>
    Public Shared Function RetrieveAllByStatuses(ByVal statusFirst As WorkOrderStatusType, ByVal statusSecond As WorkOrderStatusType) As IEnumerable(Of WorkOrder)
        Dim db As EquipmentDataContext = Database.Instance

        Return From wo In Database.Instance.WorkOrders Where wo.Status = statusFirst Or wo.Status = statusSecond
    End Function

    Public Shared Function GetByPrimaryKey(ByVal id As Integer) As WorkOrder
        Dim db As EquipmentDataContext = Database.Instance
        Dim result = From wo In db.WorkOrders Where wo.ID = id
        Dim workOrderEntity As WorkOrder = result.FirstOrDefault()
        workOrderEntity.DBContext = db
        Return workOrderEntity
    End Function

    Public Shared Function GetLatestByAsset(ByVal id As Integer) As WorkOrder
        Dim db As EquipmentDataContext = Database.Instance
        Dim result = From wo In db.WorkOrders Where wo.AssetID = id AndAlso (wo.Status = WorkOrderStatusType.AutoCreated Or wo.Status = WorkOrderStatusType.ManuallyCreated) Order By wo.DateDue Descending
        Dim workOrderEntity As WorkOrder = result.FirstOrDefault()
        If workOrderEntity IsNot Nothing Then
            workOrderEntity.DBContext = db
        End If
        Return workOrderEntity
    End Function


    Public Shared Function GetLatestByAssetFor(ByVal groupIDs As List(Of Integer), ByVal id As Integer) As WorkOrder
        Dim db As EquipmentDataContext = Database.Instance
        Dim result = From wo In db.WorkOrders Where wo.AssetID = id AndAlso (wo.Status = WorkOrderStatusType.AutoCreated Or wo.Status = WorkOrderStatusType.ManuallyCreated) AndAlso _
                            (wo.WorkOrderGroups.Select(Function(g) g.GroupID).Count(Function(gID) groupIDs.Contains(gID)) > 0) _
                                                            Order By wo.DateDue Descending
        Dim workOrderEntity As WorkOrder = result.FirstOrDefault()
        If workOrderEntity IsNot Nothing Then
            workOrderEntity.DBContext = db
        End If
        Return workOrderEntity
    End Function


    ''' <summary>
    ''' Checks whether Work Order need to be created 
    ''' based on Asset and Requirement
    ''' </summary>
    ''' <param name="assetID">Asset ID</param>
    ''' <param name="req">Requirement</param>
    ''' <returns>Work Order list</returns>
    ''' <remarks></remarks>
    Public Shared Function NeedToBeCreatedBasedOn(ByVal assetID As Integer, ByVal req As Requirement) As Boolean

        Dim count As Integer = Database.Instance.WorkOrders.Count(Function(wo) wo.IsAutomaticallyCreated = True AndAlso _
                                                      wo.BasedOnRequirementID = req.ID AndAlso _
                                                        wo.AssetID = assetID AndAlso _
                                                            wo.DateDue > DateTime.UtcNow)

        Return count < 1
    End Function

    Public Function HasCompletedNotification(ByVal notification As Notification) As Boolean
        Return Database.Instance.WOCompletedNotifications.Count(Function(cn) cn.NotificationID = notification.ID AndAlso cn.WorkOrderID = Me.ID) > 0
    End Function

    Public Function GetLastCompletedNotification() As WOCompletedNotification
        Dim db As EquipmentDataContext = Database.Instance
        Dim result = From cn In db.WOCompletedNotifications Where cn.WorkOrderID = Me.ID Order By cn.CompletedDate Descending
        Dim workOrderEntity As WOCompletedNotification = result.FirstOrDefault()
        If workOrderEntity IsNot Nothing Then
            workOrderEntity.Notification.DBContext = db
        End If
        Return workOrderEntity
    End Function

#Region "Filter By Site"
    Public Shared Function RetrieveAllBySiteIDAndStatus(ByVal siteID As Integer, ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                        ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime, ByVal status As WorkOrderStatusType) As IQueryable(Of WorkOrder)


        If status = WorkOrderStatusType.Open Then
            Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where wo.Asset.SiteDepartment IsNot Nothing AndAlso _
                                                                        wo.Asset.SiteDepartment.ParentID = siteID AndAlso wo.Status <= WorkOrderStatusType.Complete
        Else
            Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where wo.Asset.SiteDepartment IsNot Nothing AndAlso _
                                                                        wo.Asset.SiteDepartment.ParentID = siteID AndAlso wo.Status = status
        End If
    End Function


    Public Shared Function RetrieveAllBySiteID(ByVal siteID As Integer, ByVal assetTypeID As Integer, ByVal departmentID As Integer, _
                                            ByVal groupID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        Return From wo In (RetrieveAll(assetTypeID, departmentID, groupID, dueDateFrom, dueDateTo)) Where wo.Asset.SiteDepartment IsNot Nothing AndAlso _
                                                                    wo.Asset.SiteDepartment.ParentID = siteID
    End Function

    Public Shared Function RetrieveAllBySiteID(ByVal siteID As Integer) As IQueryable(Of WorkOrder)

        Return From wo In Database.Instance.WorkOrders Where wo.Asset.SiteDepartment.ParentID = siteID
    End Function

    Public Shared Function RetrieveAllBySiteID(ByVal siteID As Integer, ByVal dueDateFrom As DateTime, ByVal dueDateTo As DateTime) As IQueryable(Of WorkOrder)

        Return From wo In Database.Instance.WorkOrders Where wo.DateDue >= dueDateFrom AndAlso wo.DateDue <= dueDateTo AndAlso _
                                                                    wo.Asset.SiteDepartment.ParentID = siteID
    End Function
#End Region

    Public Shared Function GetNextID() As Integer
        Dim Ids = From wo In Database.Instance.WorkOrders Select wo.ID
        If Ids.Count() = 0 Then
            Return 1
        Else
            Return Ids.Max() + 1
        End If
    End Function


    ''' <summary>
    ''' Inserting new Work Order
    ''' </summary>
    ''' <param name="entity">Work Order</param>
    ''' <remarks></remarks>
    Public Shared Sub Insert(ByVal entity As WorkOrder)

        Dim db As EquipmentDataContext = Database.Instance

        db.WorkOrders.InsertOnSubmit(entity)
        Try
            For Each measure In entity.Measures
                measure.WorkOrder = entity
                db.WorkOrderMeasures.InsertOnSubmit(measure)
            Next

            For Each atGroup In entity.Groups
                Dim workOrderATGroup As New WorkOrderGroup()
                workOrderATGroup.WorkOrder = entity
                workOrderATGroup.GroupID = atGroup.ID
                db.WorkOrderGroups.InsertOnSubmit(workOrderATGroup)
            Next

            For Each reviewGroup In entity.ReviewGroups
                Dim woReviewGroup As New WorkOrderReviewGroup()
                woReviewGroup.WorkOrder = entity
                woReviewGroup.GroupID = reviewGroup.ID
                db.WorkOrderReviewGroups.InsertOnSubmit(woReviewGroup)
            Next

            db.SubmitChanges()
        Catch ex As Exception
            logger.Error(ex.Message + ":" + ex.StackTrace)
        End Try
    End Sub

    Public Sub ChangeStatusTo(ByVal status As WorkOrderStatusType)
        Dim wo = WorkOrder.GetByPrimaryKey(Me.ID)
        wo.Status = status
        wo.Save()
    End Sub

    Public Sub MarkException(ByVal hasException As Boolean)
        Dim wo = WorkOrder.GetByPrimaryKey(Me.ID)
        wo.HasException = hasException
        wo.Save()
    End Sub

    Public Sub Approve(ByVal status As WorkOrderStatusType, ByVal byUserId As Integer)
        Dim wo = WorkOrder.GetByPrimaryKey(Me.ID)
        wo.Status = status
        wo.ApprovedDate = DateTime.UtcNow
        wo.ApprovedBy = byUserId
        wo.Save()
    End Sub


    Public Sub Save()
        If Me.DBContext IsNot Nothing Then
            Dim _dataBase As EquipmentDataContext = Me.DBContext

            Try

                Dim groups = From wog In _dataBase.WorkOrderGroups Where wog.WorkOrderID = Me.ID Select wog
                Dim reviewGroups = From worg In _dataBase.WorkOrderReviewGroups Where worg.WorkOrderID = Me.ID Select worg
                Dim woID As Integer = Me.ID

                ' Groups
                For Each woGroup In groups
                    Dim currGroup As Group = Me.Groups.SingleOrDefault(Function(g) g.ID = woGroup.GroupID)
                    If currGroup Is Nothing Then
                        _dataBase.WorkOrderGroups.DeleteOnSubmit(woGroup)
                    End If
                Next
                For Each woGroup In Me.Groups
                    Dim currGroup As WorkOrderGroup = groups.SingleOrDefault(Function(g) g.GroupID = woGroup.ID)
                    If currGroup Is Nothing Then
                        currGroup = New WorkOrderGroup() With {.GroupID = woGroup.ID, .WorkOrderID = woID}
                        _dataBase.WorkOrderGroups.InsertOnSubmit(currGroup)
                    End If
                Next

                ' Review Groups
                For Each woGroup In reviewGroups
                    Dim currGroup As Group = Me.ReviewGroups.SingleOrDefault(Function(g) g.ID = woGroup.GroupID)
                    If currGroup Is Nothing Then
                        _dataBase.WorkOrderReviewGroups.DeleteOnSubmit(woGroup)
                    End If
                Next
                For Each woGroup In Me.ReviewGroups
                    Dim currGroup As WorkOrderReviewGroup = reviewGroups.SingleOrDefault(Function(g) g.GroupID = woGroup.ID)
                    If currGroup Is Nothing Then
                        currGroup = New WorkOrderReviewGroup() With {.GroupID = woGroup.ID, .WorkOrderID = woID}
                        _dataBase.WorkOrderReviewGroups.InsertOnSubmit(currGroup)
                    End If
                Next

                _dataBase.SubmitChanges()
            Catch ex As Exception
                logger.Error(ex.Message + ":" + ex.StackTrace)
            End Try
        End If

    End Sub

#End Region

End Class
